# -*- coding: utf-8 -*-
# !/usr/bin/env python

# @Date: 2019-02-27 9:44:12
# @Author: luolisong(luolisong1996@163.com)

# 风险预警

from flask import Blueprint, g, make_response, request, send_file
from psycopg2 import connect
from psycopg2.extras import RealDictCursor
import json

from config import config
from lib import auth, funcs
from lib.table import table
from lib.validform import V, ValidateForm

app = Blueprint(__name__ + "_app", __name__)


# 连接/关闭 数据库
@app.before_request
def setupdb():
    g.conn = connect(**config.DatabaseConfig.siem)
    g.cursor = g.conn.cursor(cursor_factory=RealDictCursor)


@app.teardown_request
def unsetdb(exception):
    if g.cursor:
        g.cursor.close()
        g.cursor = None
    if g.conn:
        g.conn.close()
        g.conn = None


# 风险管理表格数据接口
@app.route('/api/riskwarning/getdata', methods=['post'])
@auth.permission('warning')
def get_riskwarning_tableData():
    try:
        tb = table()
        g.cursor.execute("""select count(1) as total
                            from (
                                select d2t(a.submit_time) as asubmit_time,
                                  a.risk_event_id as arisk_event_id,
                                  a.analysis_id as aanalysis_id,
                                  a.risk_event_name as arisk_event_name,
                                  a.risk_event_type as arisk_event_type,
                                  a.risk_event_level as arisk_event_level,
                                  a.early_warn as aearly_warn,b.ainfluence
                                from h_risk_event_info as a
                                left join (
                                    select a.risk_event_id,
                                        case when count(distinct g.agency_id)=0 then '-'
                                        else count(distinct g.agency_id)||'个机构'||
                                              count(distinct f.info_sys_id)||'个系统'||
                                              count(distinct f.sys_node_id)||'个节点'
                                    end as ainfluence
                                    from h_risk_event_info as a
                                    left join h_event_element_info as b on a.risk_event_id = b.risk_event_id
                                    left join h_event_element_detail as c on b.element_id = c.element_id
                                    left join h_system_node as f on c.sys_id=f.sys_node_id
                                    left join h_information_system as g on f.info_sys_id=g.info_sys_id
                                    group by a.risk_event_id
                                )b
                                on a.risk_event_id=b.risk_event_id
                                %s
                            ) a
                            """ % (tb.where(['a.risk_event_name'], "a.is_commit='1' and a.state='1'")))
        total = g.cursor.fetchone()['total']
        g.cursor.execute("""select d2t(a.submit_time) as asubmit_time,
                              a.risk_event_id as arisk_event_id,
                              a.analysis_id as aanalysis_id,
                              a.risk_event_name as arisk_event_name,
                              a.risk_event_type as arisk_event_type,
                              a.risk_event_level as arisk_event_level,
                              a.early_warn as aearly_warn,b.ainfluence
                            from h_risk_event_info as a
                            left join (
                                select a.risk_event_id,
                                    case when count(distinct g.agency_id)=0 then '-'
                                    else count(distinct g.agency_id)||'个机构'||
                                          count(distinct f.info_sys_id)||'个系统'||
                                          count(distinct f.sys_node_id)||'个节点'
                                end as ainfluence
                                from h_risk_event_info as a
                                left join h_event_element_info as b on a.risk_event_id = b.risk_event_id
                                left join h_event_element_detail as c on b.element_id = c.element_id
                                left join h_system_node as f on c.sys_id=f.sys_node_id
                                left join h_information_system as g on f.info_sys_id=g.info_sys_id
                                group by a.risk_event_id
                            )b
                            on a.risk_event_id=b.risk_event_id
                            %s %s %s %s""" % (tb.where(['a.risk_event_name'], "a.is_commit='1' and a.state='1'"), tb.orderBy(), tb.offset(), tb.limit()))
        tableData = g.cursor.fetchall()
        g.cursor.execute("""select
                                risk_event_type,
                                count(risk_event_type)
                            from h_risk_event_info
                            where is_commit='1'
                            group by risk_event_type""")
        echarts = g.cursor.fetchall()
        g.cursor.execute("""select
                            coalesce(sum(case when early_warn='0' then 1 else 0 end), 0) as nowaring,
                            coalesce(sum(case when early_warn='1' then 1 else 0 end), 0) as alwaring
                          from h_risk_event_info where is_commit='1'""")
        echartsone = g.cursor.fetchone()
        # 15天里风险事项个数变化
        g.cursor.execute("""select case when b.all_risk_cnt is null then 0 else b.all_risk_cnt end as all_risk_cnt,
                                case when b.analysis_risk_cnt is null then 0 else b.analysis_risk_cnt end as analysis_risk_cnt,
                                a.r_time
                            from (
                                WITH RECURSIVE t(r_time) AS (
                                    SELECT date_trunc('hour',CURRENT_TIMESTAMP-interval '1 day')
                                  UNION ALL
                                    SELECT r_time-interval '1 day' FROM t
                                )
                                SELECT substr(d2t(r_time),1,10) as r_time
                                FROM t
                                LIMIT 15
                            )a
                            left join (
                                select riskwarn_cnt as all_risk_cnt,warn_num as analysis_risk_cnt,substr(d2t(static_date),1,10) as r_time
                                from h_riskitemwarn_data
                                order by substr(d2t(static_date),1,10) desc
                                limit 15
                            )b
                            on a.r_time=b.r_time
                            order by a.r_time asc""")
        timeecharts = g.cursor.fetchall()
        # 如今风险事项求和
        g.cursor.execute("""select count(risk_event_id) as sum from h_risk_event_info where is_commit = '1' and state = '1'""")

        sumrisk = g.cursor.fetchone()
        return json.dumps({
            "status": "success",
            "data": {
                "current": tb.current(),
                "dataSource": {
                    "total": total,
                    "tableData": tableData
                },
                "echarts": echarts,
                "echartsone": echartsone,
                "timeecharts": timeecharts,
                "sumrisk": sumrisk
            }
        })
    except Exception, e:
        return json.dumps({"status": "except", "warn": str(e)})


# '/api/Derailsall/unitnames'
@app.route('/api/Derailsall/unitnames', methods=['post'])
@auth.permission('warning')
def get_Derailsall_unitnames():
    try:
        g.cursor.execute("""select agency_id as key,agency_name as label,supervision_agency_id as parent
                            from sys_agency_info
                            where state = '1' and node_type_id='1' and agency_id !='0'""")
        allunitnames = g.cursor.fetchall()
        allunitnamestree = funcs.uintTree(allunitnames)
        return json.dumps({
            "status": "success",
            "data": {
                "allunitnamestree": allunitnamestree,
                "allunitnames": allunitnames
            }
        })
    except Exception, e:
        return json.dumps({"status": "except", "warn": str(e)})


# 风险预警预警风险事项
@app.route('/api/riskwarning/yujing', methods=['post'])
@auth.permission('warning')
def get_riskwarning_yujing(_currUser):
    try:
        form = ValidateForm(
            id=['风险事项id', V.required()],
            warnunit=['预警机构集', V.required()]
        )
        (flag, data) = form.validate()
        if not flag:
            data['status'] = 'fail'
            return json.dumps(data)
        data['personid'] = _currUser['user']['person_id']
        g.cursor.execute("""insert into h_risk_early_warn (
                                risk_event_id, warn_person, warn_time
                            ) values (
                                %(id)s, %(personid)s, now()
                            ) returning warn_id""" % (data))
        data['warn_id'] = g.cursor.fetchone()['warn_id']
        g.cursor.execute("""insert into h_early_warn_aim(
                                warn_id,aim_id,aim_name,r_time
                            )
                            select %(warn_id)s as warn_id, agency_id as aim_id,agency_name as aim_name, now() as r_time
                            from sys_agency_info
                            where agency_id in (select Unnest(string_to_array(%(warnunit)s,',')))
                        """, data)
        g.cursor.execute("""update h_risk_event_info set early_warn = '1' where risk_event_id = %(id)s""" % (data))
        g.conn.commit()
        return json.dumps({"status": "success", "msg": "预警成功"})
    except Exception, e:
        return json.dumps({"status": "except", "msg": '异常', "warn": str(e)})


# 风险预警详情表格数据接口
@app.route('/api/riskwarning/Derails/getInitil', methods=['post'])
@auth.permission('warning')
def get_riskwarningDerails_tableData():
    try:
        form = ValidateForm(
            id=['风险事项编号', V.required()]
        )
        (flag, data) = form.validate()
        if not flag:
            data['status'] = 'fail'
            return json.dumps(data)
        # 基本信息
        g.cursor.execute("""select
                                row_to_json(a) as basics,
                                json_agg(b) as correlation
                                from h_risk_event_info as c
                                left join (
                                    select a.risk_event_id,
                                        a.risk_event_name,
                                        a.risk_event_level,
                                        case when a.analysis_basis is null or a.analysis_basis='' then '' else a.analysis_basis end as analysis_basis,
                                        a.risk_event_type,
                                        case when a.risk_event_content is null or a.risk_event_content='' then '' else a.risk_event_content end as risk_event_content,
                                        case when a.remark is null or a.remark='' then '' else a.remark end as remark,
                                        a.r_time,
                                        d.person_name as r_person,
                                        case when a.solution is null or a.solution='' then '' else a.solution end as solution,
                                        case when b.warn_id is null then 0 else 1 end as warn
                                    from h_risk_event_info as a
                                    left join sys_person as d
                                    on a.r_person = d.person_id
                                    left join h_risk_early_warn b
                                    on a.risk_event_id=b.risk_event_id
                                ) as a on c.risk_event_id = a.risk_event_id
                                left join (
                                select
                                    a.risk_event_id,
                                    c.event_name,
                                    c.threaten_level,
                                    c.event_desc
                                    from
                                    h_risk_event_info as a
                                    left join h_analysis_info as b on a.analysis_id = b.analysis_id
                                    left join h_attack_eventinfo as c on b.event_id = c.event_id
                                )as b on c.risk_event_id = b.risk_event_id
                                where c.risk_event_id = %(id)s
                                group by a""", data)
        dataSource = g.cursor.fetchone()
        dataSource['warn'] = dataSource['basics']['warn']
        # 相关安全事件和相关情报事件
        g.cursor.execute("""select a.element_name,
                                case when string_agg(distinct e.sys_node_name,',') is not null then string_agg(distinct e.sys_node_name,',') else '-' end as effect_sys_node,
                                case when string_agg(distinct f.info_sys_name,',') is not null then string_agg(distinct f.info_sys_name,',') else '-' end as effect_info_sys,
                                case when string_agg(distinct g.agency_name,',') is not null then string_agg(distinct g.agency_name,',') else '-' end as effect_agency,
                                coalesce(json_agg(distinct c) filter (where c.id is not null), '[]') as history,
                                coalesce(json_agg(distinct d) filter (where d.id is not null), '[]') as attack
                            from h_event_element_info as a
                            left join h_event_element_detail as b
                            on a.element_id = b.element_id
                            left join h_system_node as e
                            on b.sys_id = e.sys_node_id
                            left join h_information_system as f
                            on f.info_sys_id = e.info_sys_id
                            left join sys_agency_info as g
                            on g.agency_id = f.agency_id
                            left join (
                                select distinct id, a.risk_event_id, a.related_element_name,
                                    b.risk_event_name,
                                    b.risk_event_level,
                                    b.risk_event_type,
                                    case when b.risk_event_content is null or b.risk_event_content='' then '-' else b.risk_event_content end as risk_event_content
                                    from h_event_related_analysis_info as a
                                    left join h_risk_event_info as
                                    b on b.risk_event_id::varchar = a.related_id
                                where a.related_type = '3'
                            ) as c
                            on c.related_element_name = a.element_name and a.risk_event_id = c.risk_event_id
                            left join (
                                select distinct a.id,a.risk_event_id, a.related_element_name,
                                    b.event_name as risk_event_name,
                                    b.attack_num,
                                    d2t(b.start_time) as start_time,
                                    d2t(b.new_time) as new_time,
                                    b.threaten_level as risk_event_level,
                                    case when b.event_desc is null or b.event_desc='' then '-' else b.event_desc end as risk_event_content
                                from
                                    h_event_related_analysis_info as a
                                left join h_attack_eventinfo as b
                                    on b.event_id = a.related_id
                                where a.related_type = '4'
                            ) as d
                            on d.related_element_name = a.element_name and a.risk_event_id = d.risk_event_id
                            where a.risk_event_id = %(id)s
                            group by a.element_name""", data)
        correlation = g.cursor.fetchall()
        # 操作记录流水表
        g.cursor.execute("""select d.person_name as user_name,
                                   max(d2t(a.warn_time)) as r_time,
                                   json_agg(b.aim_name) as warn_person
                               from h_risk_early_warn as a
                               left join h_early_warn_aim as b on a.warn_id = b.warn_id
                               left join sys_person as d on a.warn_person = d.person_id
                               where a.risk_event_id = %(id)s
                               group by d.person_name
                               order by max(d2t(a.warn_time)) desc""", data)
        operate = g.cursor.fetchone()

        return json.dumps({
            "status": "success",
            "data": {
                "dataSource": dataSource,
                "correlation": correlation,
                "operate": operate
            }
        })
    except Exception, e:
        return json.dumps({"status": "except", "msg": '异常', "warn": str(e)})


# 根据路径下载文件
@app.route('/api/riskwarning/download', methods=['POST'])
@auth.permission('warning')
def download_warning():
    try:
        form = ValidateForm(
            url=['依据路径', V.required()]
        )
        (flag, data) = form.validate()
        if not flag:
            data['status'] = 'fail'
            return json.dumps(data)

        response = make_response(send_file(data['url'].encode('utf8')))
        response.headers["Content-Disposition"] = "attachment; filename={}".format(data['url'].split('/')[-1])
        return response
    except Exception:
        return json.dumps({"status": "fail", "msg": "下载失败"})


# 风险详情表格数据接口
@app.route('/api/riskwarning/Derails/larmlog', methods=['post'])
@auth.permission('warning')
def get_riskwarningDerails_larmlog():
    try:
        form = ValidateForm(
            id=['风险事项编号', V.required()]
        )
        (flag, data) = form.validate()
        if not flag:
            data['status'] = 'fail'
            return json.dumps(data)
        data['dataSource'] = {
            "statistical": {},  # 摘要信息
            "warninfos": []  # 告警信息（机构归并）
        }
        # 附录摘要信息统计
        g.cursor.execute("""select
                              a.risk_event_id,
                              coalesce(count(c.element_id),0) as sumelement,
                              coalesce(sum(c.sys_node_num),0) as sys_node_num,
                              coalesce(sum(c.info_sys_num),0) as info_sys_num
                           from h_risk_event_info as a
                           left join h_event_element_info as b on a.risk_event_id = b.risk_event_id
                           left join h_event_effect_info as c on b.element_id = b.element_id
                           where a.risk_event_id = %(id)s
                           group by
                               a.risk_event_id """, data)
        data['dataSource']['statistical'] = g.cursor.fetchone()
        # 获取对应风险事项下的要素id
        g.cursor.execute("""select
                            element_id
                          from h_event_element_info
                            where risk_event_id = %(id)s""", data)
        element = g.cursor.fetchall()

        for key in element:
            # 获取对应要素下的影响节点信息
            g.cursor.execute("""select
                              log_id,
                              d2t(ts) as time,
                              sys_name,
                              log_level,
                              tag
                            from
                            h_event_element_detail
                            where element_id = %s""", [key['element_id']])
            alarmdata = g.cursor.fetchall()
            # 获取对应要素下的影响信息统计
            g.cursor.execute("""select
                                    distinct a.element_name,
                                    b.sys_node_num
                                from
                                    h_event_element_info as a
                                left join h_event_effect_info as b on a.element_id = b.element_id
                                    where a.element_id = '%s'""", [key['element_id']])
            unitname = g.cursor.fetchall()

            data['dataSource']['warninfos'].append(
                {
                    'unitname': unitname,
                    'data': alarmdata
                }
            )
        return json.dumps({
            "status": "success",
            "data": data
        })
    except Exception, e:
        return json.dumps({"status": "except", "msg": '异常', "warn": str(e)})